/**
 * 
 */
package com.service.tag.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.log4j.Logger;

import com.pub.db.DBConnectionManager;
import com.pub.exception.DBException;
import com.pub.exception.NullEnvironmentVariableException;
import com.service.tag.dao.TagDao;
import com.service.tag.domain.LogTagRequestGoogle;
import com.service.tag.domain.Tag;
import com.service.tag.domain.TagAttribute;
import com.service.tag.key.TagKey;

/**
 * @author iling
 *
 */
public class TagImpl implements TagDao
{
	private static Logger logger;
	private DBConnectionManager dbcm;
	
	private String DBTag;
	
	static
	{
		logger = Logger.getLogger(TagImpl.class.getName());
	}
	
	/**
	 * 
	 */
	public TagImpl(String DBTag) 
	{
		this.DBTag = DBTag;
		
		try {
			dbcm	= DBConnectionManager.getInstance(TagImpl.class.getName());
		} catch (NullEnvironmentVariableException e) {
			e.printStackTrace();
		}
	}

	public int insert(Tag tag)
	{
		int id = 0;
		
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs	= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		
		sbSql.append("INSERT INTO tag SET modifiedTime = UTC_TIMESTAMP(), Operator='auto', Module='");
		
		
		sbSql.append("'");

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			sm.executeUpdate(sbSql.toString(), Statement.RETURN_GENERATED_KEYS);
			rs = sm.getGeneratedKeys();
			if(rs.next()){
				id = rs.getInt(1);
				
				tag.setId(id);
			}
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (rs != null){
	        	try{
	        		rs.close();
				} catch (Exception e){
				}
	        }
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("insert method return id value : " + id);
		
		return id;
	}
	
	public int update(Tag tag)
	{
		int updateCount = 0;
		
		Connection conn = null;
		Statement sm 	= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		
		sbSql.append("INSERT INTO tag SET Module='");
		
		
		sbSql.append("' ON DUPLICATE KEY UPDATE Commments=VALUES(Commments), Status=VALUES(Status), orders=VALUES(orders), Module=VALUES(Module), Operator=VALUES(Operator), ModifiedTime=VALUES(ModifiedTime)");

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			updateCount = sm.executeUpdate(sbSql.toString());
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("update method return update count : " + updateCount);
		
		return updateCount;
	}
	
	public void insertAttributes(List<Tag> tags) throws DBException
	{
		for(Tag tag: tags){
			insertAttributes(tag.getAttributes());
		}
	}
	
	public void insertAttributes(Map<TagKey, TagAttribute> tagAttris) throws DBException
	{
		for(Entry<TagKey, TagAttribute> entry: tagAttris.entrySet()){
			insertAttributes(entry.getValue());
		}
	}
	
	public int insertAttributes(TagAttribute tagAttri) throws DBException
	{
		int updateCount = 0;
		
		Connection conn = null;
		Statement sm 	= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		
		sbSql.append("INSERT INTO tag_attributes SET sourceId=");
		sbSql.append(tagAttri.getPrimaryKey().getSourceId());
		sbSql.append(", tagId=");
		sbSql.append(tagAttri.getPrimaryKey().getTagId());
		sbSql.append(", attributeId=");
		sbSql.append(tagAttri.getPrimaryKey().getAttributeId());
		sbSql.append(", attributeValue='");
		sbSql.append(tagAttri.getAttributeValue());
		
		sbSql.append("' ON DUPLICATE KEY UPDATE attributeValue=VALUES(attributeValue)");

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			updateCount = sm.executeUpdate(sbSql.toString());
		} catch (Exception e){
			throw new DBException("Error SQL : " + sbSql.toString(), e);
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("update method return update count : " + updateCount);
		
		return updateCount;
	}
	
	public int insertRequestGoogleLog(LogTagRequestGoogle tagLog) throws DBException
	{
		int updateCount = 0;
		
		Connection conn = null;
		Statement sm 	= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		
		sbSql.append("INSERT INTO log_tagRequestGoogle SET tagId=");
		sbSql.append(tagLog.getTagId());
		sbSql.append(", searchCount=");
		sbSql.append(tagLog.getSearchCount());
		sbSql.append(", status=");
		sbSql.append(tagLog.getStatus());
		
		sbSql.append(" ON DUPLICATE KEY UPDATE searchCount= searchCount + 1");

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			updateCount = sm.executeUpdate(sbSql.toString());
		} catch (Exception e){
			throw new DBException("Error SQL : " + sbSql.toString(), e);
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("update method return update count : " + updateCount);
		
		return updateCount;
	}
	
	@Override
	public Tag getTag(int id) throws DBException
	{
		Tag rt = null;
		
		Connection 	conn	= null;
		Statement	sm		= null;
		ResultSet	rs		= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		sbSql.append("SELECT Operator, orders, Commments, Status, CreatedTime, ModifiedTime, CountryID, BatchID FROM ModuleRunLog WHERE ID ='");
		sbSql.append(id);
		sbSql.append("'");
		
        
		try {
			conn 	= dbcm.getConnection(DBTag);
			sm		= conn.createStatement();
			rs		= sm.executeQuery(sbSql.toString());
			if(rs.next()){
				rt = new Tag();
				
				rt.setId(id);
				
			}
		} catch (Exception e) {
			throw new DBException("Error SQL : " + sbSql.toString(), e);
		}
		     
		return rt;
	}

	@Override
	public List<Tag> getCustom(String where) throws DBException
	{
		List<Tag> mappingList	= new LinkedList<Tag>();
		
		Tag rt = null;
		
		Connection 	conn	= null;
		Statement	sm		= null;
		ResultSet	rs		= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		sbSql.append("SELECT id, name FROM tag");
		
		if(where != null)
			sbSql.append(where);
		
		try {
			conn 	= dbcm.getConnection(DBTag);
			sm		= conn.createStatement();
			rs		= sm.executeQuery(sbSql.toString());
			
			while(rs.next()){
				rt = new Tag();
				
				rt.setId(rs.getInt(1));
				rt.setName(rs.getString(2));
				
				mappingList.add(rt);
			}
		} catch (Exception e) {
			throw new DBException("Error SQL : " + sbSql.toString(), e);
		} finally {
			if(rs != null){
	            try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
        }
		     
		return mappingList;
	}
	
	@Override
	public void close() 
	{
		dbcm.release(TagImpl.class.getName());
	}

}
